﻿using System.Data;
using System.Data.SqlClient;

namespace Adams.Public.DataAccess
{
    public static class Donations
    {
        public static Adams.Shared.Entities.Finance.CreditCard GetCreditCard(Public.DataTransfer.MemberInfo input)
        {
            var data = new Adams.Shared.Entities.Finance.CreditCard();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT *
FROM Finance.CreditCards
WHERE MemberID = @MemberID
", CommandType.Text,
    DB.CreateParameter("@MemberID", input.MemberID)))
            {
                while (dr.Read())
                {
                    data.ID = dr["CreditCardID"].DbInt();
                    data.CardExpireMonth = dr["CardExpireMonth"].DbInt();
                    data.CardExpireYear = dr["CardExpireYear"].DbInt();
                 }    
            }

            return data;
        }

        public static Adams.Shared.Entities.Donations.Bucket GetBucket(Public.DataTransfer.Donations.GetBucketInput input)
        {
            var data = new Adams.Shared.Entities.Donations.Bucket();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT *
FROM Donations.Buckets
WHERE BucketID = @BucketID
", CommandType.Text,
    DB.CreateParameter("@BucketID", input.BucketID)))
            {
                dr.Read();
                data.ID = dr["BucketID"].DbInt();
                data.Name = dr["Name"].DbString();
                data.IsDefault = dr["IsDefault"].DbBool();
                data.AllowRecurring = dr["AllowRecurring"].DbBool();
            }

            return data;
        }

        public static Adams.Public.DataTransfer.Donations.GetDonations GetDonations(Public.DataTransfer.MemberInfo input)
        {
            var data = new Adams.Public.DataTransfer.Donations.GetDonations();
            data.OneTimes = new Shared.Entities.Donations.OneTimeCollection();
            data.Recurrings = new Shared.Entities.Donations.RecurringCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT O.* , B.Name, B.IsDefault
FROM Donations.OneTime O INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID
WHERE MemberID = @MemberID AND StatusID > 1
ORDER BY EnteredOn DESC

SELECT O.* , B.Name, B.IsDefault
FROM Donations.Recurring O INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID
WHERE MemberID = @MemberID AND StatusID > 1
ORDER BY EnteredOn DESC
", CommandType.Text,
    DB.CreateParameter("@MemberID", input.MemberID)))
            {
                while (dr.Read())
                {
                    var onetime = new Shared.Entities.Donations.OneTime();
                    onetime.ID = dr["DonationID"].DbInt();
                    onetime.Amount = dr["Amount"].DbDecimal();
                    onetime.EnteredOn = dr["EnteredOn"].DbDate();
                    onetime.ProcessedOn = dr["ProcessedOn"].DbDate();
                    onetime.Status = (Shared.Entities.Donations.Status)dr["StatusID"].DbInt();
                    onetime.Bucket = new Shared.Entities.Donations.Bucket() {
                        ID = dr["BucketID"].DbInt(),
                        Name = dr["Name"].DbString(),
                        IsDefault = dr["IsDefault"].DbBool()
                    };
                    data.OneTimes.Add(onetime);
                }

                dr.NextResult();
                while (dr.Read())
                {
                    var recurring = new Shared.Entities.Donations.Recurring();
                    recurring.ID = dr["DonationID"].DbInt();
                    recurring.Amount = dr["Amount"].DbDecimal();
                    recurring.EnteredOn = dr["EnteredOn"].DbDate();
                    recurring.NextProcessOn = dr["NextProcessOn"].DbDate();
                    recurring.LastProcessedOn = dr["LastProcessedOn"].DbDateNullable();
                    recurring.Status = (Shared.Entities.Donations.Status)dr["StatusID"].DbInt();
                    recurring.Bucket = new Shared.Entities.Donations.Bucket()
                    {
                        ID = dr["BucketID"].DbInt(),
                        Name = dr["Name"].DbString(),
                        IsDefault = dr["IsDefault"].DbBool()
                    };
                    data.Recurrings.Add(recurring);
                }

                return data;
            }
        }

        public static int GetDefaultBucketID()
        {
            int bucketid = DB.RunScalar(@"
SELECT BucketID 
FROM Donations.Buckets
WHERE IsDefault = 1", CommandType.Text).DbInt();
            return bucketid;
        }

        public static Shared.Entities.Donations.Status GetDonationStatus(int donationId)
        {
            Shared.Entities.Donations.Status status = (Shared.Entities.Donations.Status)DB.RunScalar(@"
SELECT StatusID 
FROM Donations.OneTime
WHERE DonationID = @DonationID", CommandType.Text,
                               DB.CreateParameter("@DonationID", donationId)).DbInt();
            return status;
        }

        public static Shared.Entities.Donations.Status GetRecurringDonationStatus(int donationId)
        {
            Shared.Entities.Donations.Status status = (Shared.Entities.Donations.Status)DB.RunScalar(@"
SELECT StatusID 
FROM Donations.Recurring
WHERE DonationID = @DonationID", CommandType.Text,
                               DB.CreateParameter("@DonationID", donationId)).DbInt();
            return status;
        }

        public static void CancelDonation(DataTransfer.Web.Areas.Membership.Donations.Index.CancelInput input)
        {
            DB.Run(@"
UPDATE Donations.OneTime 
    SET StatusID = 4
WHERE DonationID = @DonationID AND MemberID = @MemberID
", CommandType.Text,
                DB.CreateParameter("@DonationID", input.DonationID),
                DB.CreateParameter("@MemberID", input.MemberID));
        }

        public static void CancelRecurringDonation(DataTransfer.Web.Areas.Membership.Donations.Index.CancelRecurringInput input)
        {
            DB.Run(@"
UPDATE Donations.Recurring 
    SET StatusID = 4
WHERE DonationID = @DonationID AND MemberID = @MemberID
", CommandType.Text,
                DB.CreateParameter("@DonationID", input.DonationID),
                DB.CreateParameter("@MemberID", input.MemberID));
        }

        public static Adams.Shared.Entities.Donations.Recurring GetRecurring(int donationId)
        {
            var data = new Adams.Shared.Entities.Donations.Recurring();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT R.*, B.Name AS BucketName, M.Email, M.FirstName, M.LastName
FROM Donations.Recurring R 
INNER JOIN Donations.Buckets B ON R.BucketID = B.BucketID
INNER JOIN Membership.Members M ON M.MemberID = R.MemberID
WHERE R.DonationID = @DonationID
", CommandType.Text,
    DB.CreateParameter("@DonationID", donationId)))
            {
                dr.Read();
                data.ID = dr["DonationID"].DbInt();
                data.Amount = dr["Amount"].DbDecimal();
                data.Bucket = new Shared.Entities.Donations.Bucket() {
                    ID = dr["BucketID"].DbInt(),
                    Name = dr["BucketName"].DbString()
                };

                data.Member = new Shared.Entities.Membership.Member() {
                    ID = dr["MemberID"].DbInt(),
                    Email = dr["Email"].DbString(),
                    FirstName = dr["FirstName"].DbString(),
                    LastName = dr["LastName"].DbString()
                };
            }

            return data;
        }

        public static Adams.Shared.Entities.Donations.OneTime GetOneTime(int donationId)
        {
            var data = new Adams.Shared.Entities.Donations.OneTime();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT R.*, B.Name AS BucketName, M.Email, M.FirstName, M.LastName
FROM Donations.OneTime R 
INNER JOIN Donations.Buckets B ON R.BucketID = B.BucketID
INNER JOIN Membership.Members M ON M.MemberID = R.MemberID
WHERE R.DonationID = @DonationID
", CommandType.Text,
    DB.CreateParameter("@DonationID", donationId)))
            {
                dr.Read();
                data.ID = dr["DonationID"].DbInt();
                data.Amount = dr["Amount"].DbDecimal();
                data.Bucket = new Shared.Entities.Donations.Bucket()
                {
                    ID = dr["BucketID"].DbInt(),
                    Name = dr["BucketName"].DbString()
                };

                data.Member = new Shared.Entities.Membership.Member()
                {
                    ID = dr["MemberID"].DbInt(),
                    Email = dr["Email"].DbString(),
                    FirstName = dr["FirstName"].DbString(),
                    LastName = dr["LastName"].DbString()
                };
            }

            return data;
        }

        public static Adams.Shared.Entities.Donations.Quick GetQuick(int donationId)
        {
            var data = new Adams.Shared.Entities.Donations.Quick();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT R.*, B.Name AS BucketName
FROM Donations.Quick R 
INNER JOIN Donations.Buckets B ON R.BucketID = B.BucketID
WHERE R.DonationID = @DonationID
", CommandType.Text,
    DB.CreateParameter("@DonationID", donationId)))
            {
                dr.Read();
                data.ID = dr["DonationID"].DbInt();
                data.Amount = dr["Amount"].DbDecimal();
                data.Title = dr["Title"].DbString();
                data.FirstName = dr["FirstName"].DbString();
                data.LastName = dr["LastName"].DbString();
                data.Email = dr["Email"].DbString();
                data.Street = dr["Street"].DbString();
                data.Street2 = dr["Street2"].DbString();
                data.City = dr["City"].DbString();
                data.State = dr["State"].DbString();
                data.ZipCode = dr["ZipCode"].DbString();
                data.Phone = dr["Phone"].DbString();
                data.CardHolderName = dr["CardHolderName"].DbString();
                data.CardNumber = dr["CardNumber"].DbString();
                data.CardType = dr["CardType"].DbString();
                data.CardLast4Digits = dr["CardLast4Digits"].DbString();
                data.CardExpireMonth = dr["CardExpireMonth"].DbInt();
                data.CardExpireYear = dr["CardExpireYear"].DbInt();
                data.CardSecurityCode = dr["CardSecurityCode"].DbString();
                data.Bucket = new Shared.Entities.Donations.Bucket()
                {
                    ID = dr["BucketID"].DbInt(),
                    Name = dr["BucketName"].DbString()
                };
            }

            return data;
        }
    }
}
